﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;

namespace DAL
{
    //一个实体对象相当于表中一行记录
    public class ArticleDal
    {
        //增加实体对象
        public static bool Add(Article o)
        {
            string sql = string.Format("insert into Article values('{0}',{1},'{2}','{3}','{4}','{5}','{6}')", o.Title, o.ClassID == 0 ? "null" : o.ClassID.ToString(), o.Thumb, o.Brief, o.Content, o.Author, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return DBHelper.ExecuteNonQuery(sql);
        }
        //根据id删除实体对象
        public static bool Delete(int id)
        {
            string sql = "delete from Article where Id=" + id;
            return DBHelper.ExecuteNonQuery(sql);
        }
        //根据id修改实体对象
        public static bool Update(Article o)
        {
            string sql = string.Format("update Article set Title='{0}',ClassID={1},Thumb='{2}',Brief='{3}',[Content]='{4}',Author='{5}',Added_time='{6}' where Id={7}", o.Title, o.ClassID == 0 ? "NULL" : o.ClassID.ToString(), o.Thumb, o.Brief, o.Content, o.Author, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), o.Id);
            return DBHelper.ExecuteNonQuery(sql);
        }
        //万能查询（包括分页排序）
        public static List<Article> Select(string sql)
        {
            DataTable dt = DBHelper.GetDataTable(sql);//调用DBHelper类查询得到表格
            List<Article> objs = new List<Article>();//创建保存实体对象（记录行）的list集合，相当于表
            if (dt != null && dt.Rows.Count > 0)//如果查询表格有至少一条数据，转化成实体对象
            {
                foreach (DataRow row in dt.Rows)//当前行
                {
                    Article obj = new Article();//实体类对象（相当于数据表一行）
                    obj.Id = int.Parse(row["id"].ToString());
                    obj.Title = row["Title"].ToString();//将表当前行的某列值存储在对象的属性。对象属性相当于列。
                    if (row["ClassID"] != System.DBNull.Value)
                    {
                        obj.ClassID = int.Parse(row["ClassID"].ToString());
                    }
                    obj.Hits = int.Parse(row["Hits"].ToString());
                    obj.Thumb = row["Thumb"].ToString();
                    obj.Brief = row["Brief"].ToString();
                    obj.Content = row["Content"].ToString();
                    obj.Author = row["Author"].ToString();
                    obj.Added_time = row["Added_time"].ToString();
                    objs.Add(obj);//将实体对象添加到集合
                }
            }
            return objs;//返回集合，相当于表格。如果查询没有数据，则返回长度为0的集合。
        }
        //  SelectAll(" id=1")根据id查询
        //  SelectAll(" name='{0}'")根据id查询
        //根据 条件 查询 所有满足条件的记录  万能查询（除了分页）
        public static List<Article> SelectAll(string where)
        {
            string sql = "select * from Article ";
            if (!string.IsNullOrEmpty(where))//如果传递了条件查询参数，则拼接条件语句
            {
                sql = sql + " where " + where;
            }
            return Select(sql);//
        }
        //查询前多少条数据
        public static List<Article> SelectTop(int top, string where,string orderby)
        {
            string sql = string.Format("select top {0} * from Article ", top);
            if (!string.IsNullOrEmpty(where))//如果传递了条件查询参数，则拼接条件语句
            {
                sql = sql + " where " + where;
            }
            if (!string.IsNullOrEmpty(orderby))//如果传递了条件查询参数，则拼接条件语句
            {
                sql = sql + " "+orderby;
            }
            return Select(sql);//
        }
        //根据id查询单个实体对象
        public static Article SelectOne(int id)
        {
            List<Article> objs = SelectAll(" Id=" + id);//调用万能查询方法 传递参数 参数满足条件的记录
            if (objs.Count > 0)//如果查询结果大于0 则取第一个 返回
            {
                return objs[0];
            }
            return null;
        }

        //获取记录总数
        public static int GetTotal()
        {
            string sql = "select count(*) from Article";
            object r = DBHelper.ExecuteScalar(sql);
            return (int)r;
        }
        //分页查询
        public static List<Article> SelectAllByPage(int pageSize, int PageNo)
        {
            string sql = "select top " + pageSize + " * from Article where ID not in ("
              + " select top " + pageSize * (PageNo - 1) + " ID  from Article )";
            return Select(sql);
        }
    }
}
